package org.someone.office;

import org.apache.commons.compress.utils.Lists;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Shape;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.SheetVisibility;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.PaneInformation;
import org.apache.poi.xddf.usermodel.chart.XDDFChart;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xslf.usermodel.XSLFChart;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFDrawing;
import org.apache.poi.xssf.streaming.SXSSFPicture;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFGraphicFrame;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheetConditionalFormatting;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbookFactory;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.someone.common.CustomMaths;

import java.io.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {
    public final static String excelColRegex = "[A-Z]+";
    public final static String excelCellRegex = "^[A-Z]+\\d+$";    //正则表达式 匹配excel单元格名称
    public final static String excelFindCellRegex = "[$]?[A-Z]+\\d+";    //正则表达式 匹配excel单元格名称
    
    /**
     * 自动合并单元格
     * @param sheet
     * @param region
     */
	public static void autoMergeCol4Range(XSSFSheet sheet, CellRangeAddress region){
		int istartcolnum = region.getFirstColumn();
		int iendcolnum = region.getLastColumn();
		for(int icol = istartcolnum; icol< iendcolnum; icol++){
			boolean isextendpercell = true;
			if(icol ==0){
				isextendpercell = false;
			}else{
				isextendpercell = !isNullRange(sheet,region.getFirstRow(),region.getLastRow(),icol-1,icol-1);
			}
			mergeEqualsRow4Col(sheet, region.getFirstRow(), region.getLastRow(), icol, isextendpercell);
		}
	}

	/**
	 * copy 一个cell 内容到另一个cell
	 * @param templateCell
	 * @param targetCell
	 */
	public static void copyCell(Cell templateCell, Cell targetCell){
	    targetCell.setCellStyle(templateCell.getCellStyle());
	    targetCell.setCellType(templateCell.getCellType());
	    switch (templateCell.getCellType()) {
	        case NUMERIC: // 数字
	            targetCell.setCellValue(templateCell.getNumericCellValue());
	            break;
	        case STRING: // 字符串Cell
	            targetCell.setCellValue(templateCell.getStringCellValue());
	            break;
	        case BOOLEAN: // Boolean
	            targetCell.setCellValue(templateCell.getBooleanCellValue());
	            break;
	        case FORMULA: // 公式
	            copyFormular(templateCell, targetCell, true);
	            break;
	        case BLANK: // 空值
	            //targetCell.setCellValue("");
	            break;
	        case ERROR: // 故障
	            //value = "非法字符";
	            break;
	        default:
	            //value = "未知类型";
	            break;
	    }
	}

	/**
	 * 复制单元格的公式到另一个单元格，自动计算替换公式中列的关系
	 * @param templateCell
	 * @param targetCell
	 * @param isAutoRepformular
	 */
	public static void copyFormular(Cell templateCell, Cell targetCell, boolean isAutoRepformular){
	    if(isAutoRepformular){
	        String oldFormular = templateCell.getCellFormula();
	        int tprownum = templateCell.getRowIndex();
	        int tpcolnum = templateCell.getColumnIndex();
	        int tgrownum = targetCell.getRowIndex();
	        int tgcolnum = targetCell.getColumnIndex();
	        String value = "";
	        Pattern pattern = Pattern.compile(excelFindCellRegex);
	        Matcher matcher = pattern.matcher(oldFormular);
	        int lastEnd = 0;
	        while(matcher.find()){
	            String oldCellName = matcher.group();
	            String newCellName = getExcelCellName(oldCellName, tgrownum-tprownum, tgcolnum-tpcolnum);
	            value = value + oldFormular.substring(lastEnd, matcher.start()) + newCellName;
	            lastEnd = matcher.end();
	        }
	        value = value + oldFormular.substring(lastEnd);
	        targetCell.setCellFormula(value);
	    }else{
	        targetCell.setCellFormula(templateCell.getCellFormula());
	    }
	    XSSFFormulaEvaluator xeval = new XSSFFormulaEvaluator((XSSFWorkbook) targetCell.getSheet().getWorkbook());
	    xeval.evaluateFormulaCell(targetCell);
	}

	/**
	 * 将一行的内容copy至另一行中
	 * @param templateRow
	 * @param targetRow
	 */
	public static void copyRowCells(Row templateRow, Row targetRow){
	    for(int i= templateRow.getFirstCellNum(); i< templateRow.getLastCellNum(); i++){
	
	        Cell oldcell = templateRow.getCell(i);
	        if(oldcell !=null){
	            Cell newcell = targetRow.createCell(i);
	            copyCell(oldcell, newcell);
	        }
	    }
	}
	
	/**
	 * 获得cell中的值并转换为String 类型
	 * @param cell
	 * @return
	 */
	public static String getCellValue2String(Cell cell){
		String strval = null;
		if(cell != null){
			switch (cell.getCellType()){
				case NUMERIC: // 数字
					strval = String.valueOf(cell.getNumericCellValue());
					break;
				case STRING: // 字符串Cell
					strval = String.valueOf(cell.getStringCellValue());
					break;
				case BOOLEAN: // Boolean
					strval = String.valueOf(cell.getBooleanCellValue());
					break;
				case FORMULA: // 公式
					strval = cell.getRichStringCellValue().getString();
					break;
				case BLANK: // 空值
					//targetCell.setCellValue("");
					break;
				case ERROR: // 故障
					//value = "非法字符";
					break;
				default:
					//value = "未知类型";
					break;
			}
		}
		return strval;
	}

	/**
	 * 根据单元格名得到改变位置后的单元格名
	 * @param oldCellName
	 * @param rowoffset
	 * @param coloffset
	 * @return
	 */
	public static String getExcelCellName(String oldCellName, int rowoffset, int coloffset){
	    String newCellName = oldCellName;
	    Pattern pattern = Pattern.compile(excelCellRegex);
	    Matcher matcher = pattern.matcher(oldCellName);
	    if(matcher.matches()) {
	        pattern = Pattern.compile(excelColRegex);
	        matcher = pattern.matcher(oldCellName);
	        while(matcher.find()) {
	            String colname = matcher.group();
	            int rownum = Integer.parseInt(oldCellName.substring(matcher.end()));
	            String newcolname = CellReference.convertNumToColString(CellReference.convertColStringToIndex(colname) + coloffset);
	            int newrownum = rownum + rowoffset;
	            newCellName = newcolname + newrownum;
	            //System.out.println(newcolname + newrownum);
	        }
	    }
	    return newCellName;
	}

	/**
	 * 获得需要循环得row
	 * @param sheet
	 * @param map
	 * @return
	 */
	public static List<Row> getLoopRows(XSSFSheet sheet, Map<String, Object> map){
	    List<Row> rowList = new ArrayList<Row>();
	    Iterator<Row> rowIterator = sheet.iterator();
	    while(rowIterator.hasNext()){
	        Row row = rowIterator.next();
	        if(isLoopRow(row, map)){
	            rowList.add(row);
	        }
	    }
	    return rowList;
	}

	/**
     * 获得单元格对应的合并区域
     * @param cell
     * @return
     */
    public static CellRangeAddress getMergedRegion(Cell cell){
		CellRangeAddress range = null;
		if(cell == null) return range;
		List<CellRangeAddress> listRegion = cell.getSheet().getMergedRegions();
		for(int i=0; i< listRegion.size(); i++){
			CellRangeAddress rangeTmp = listRegion.get(i);
			if(rangeTmp.isInRange(cell)){
				range = rangeTmp;
				break;
			}
		}
		return range;
	}
	
    /**
	 * 判断该行是否是循环行，该行的第一列是否有变量且该变量在map中的值是list
	 * @param row
	 * @param map
	 * @return
	 */
	public static boolean isLoopRow(Row row, Map<String, Object> map){
	    boolean isLoopRow = false;
	    if(row.getLastCellNum()>0 || row.getPhysicalNumberOfCells()>0){
	        Cell cell = row.getCell(row.getFirstCellNum());
	        if(null!=cell&&cell.getCellType() == CellType.STRING){
		        String strValue = cell.getStringCellValue();
		        if(VarUtil.isVar(strValue, VarUtil.defaultRegex)){
		            String strcontent = VarUtil.getVar(strValue, VarUtil.defaultRegex);
		            if(strcontent!=null){
		                strcontent = strcontent.substring(2,strcontent.length()-1);
		                Object obj = map.get(strcontent);
		                isLoopRow = obj!=null && obj instanceof List;
		            }
		        }
	        }
	    }
	    return isLoopRow;
	}

	/**
	 * 判断是否时null的区域
	 * @param sheet
	 * @param istartrow
	 * @param iendrow
	 * @param istartcol
	 * @param iendcol
	 * @return
	 */
	public static boolean isNullRange(XSSFSheet sheet, int istartrow, int iendrow, int istartcol, int iendcol){
		for(int irow = istartrow; irow <= iendrow; irow++){
			Row row = sheet.getRow(irow);
			if(row != null){
	    		for(int icol = istartcol; icol <= iendcol; icol++){
	    			Cell cell = row.getCell(icol);
	    			if(cell!=null){
	    				if(cell.getCellType() == CellType.STRING){
	    					if(cell.getStringCellValue() != null && !"".equals(cell.getStringCellValue())){
	    						return false;
	    					}
	    				}else
		    			if(cell.getCellType() != CellType.BLANK){
		    				return false;
		    			}
	    			}
	    		}
			}
		}
		return true;
	}

	/**
     * 对某一列上数据相等的字符类型的行进行合并
     * @param sheet		表格
     * @param istartrow	开始行
     * @param iendrow	结束行
     * @param icol		合并列
     * @param isextendpercol	是否继承前一列的合并状态
     */
	public static void mergeEqualsRow4Col(XSSFSheet sheet, int istartrow, int iendrow, int icol, boolean isextendpercol){
		//判断参数起始行是否大于参数终止行，如果大于则将起始行、终止行互换
		if(istartrow > iendrow){
			int itmp = iendrow;
			iendrow = istartrow;
			istartrow = itmp;
		}
		//判断参数给出起始行是否大于sheet中的起始行，如果大于则以sheet中起始行为准
		if(sheet.getFirstRowNum()>istartrow){
			istartrow = sheet.getTopRow();
		}
		//判断参数给出终止行是否大于sheet中的终止行，如果大于则以sheet中终止行为准
		if(sheet.getLastRowNum()<iendrow){
			iendrow = sheet.getLastRowNum();
		}
		//如果调整后的起始行与终止行一致停止合并动作
		if(istartrow == iendrow){
			return;
		}
		//如果给出的合并列数大于sheet中的列数，则停止合并动作
		if(sheet.getRow(istartrow).getLastCellNum()<icol){
			return;
		}
		int istartmergerow = istartrow;
		CellRangeAddress percellmergerange = null;
		//第一列不需要判定isextendpercol
		if(icol==0){
			isextendpercol = false;
		}
		if(isextendpercol){
			percellmergerange = getMergedRegion(sheet.getRow(istartrow).getCell(icol-1));
		}
		for(int irow=istartrow ; irow <= iendrow; irow++){
			boolean ismergebreak = false;
//			Row lastrow = sheet.getRow(irow-1);	//上一行
			Row currow = sheet.getRow(irow);	//当前行
			Row nextrow = sheet.getRow(irow+1);	//下一行
			if(nextrow == null){
				ismergebreak = true;
			}else{
//				Cell lastcell = lastrow.getCell(icol);	//上一行cell
				Cell curcell = currow.getCell(icol);	//当前行cell
				Cell nextcell = nextrow.getCell(icol);	//下一行cell
				//如果单元格为null对象则继续循环
				if(curcell==null) continue;
				//非字符行不进行合并
				if(curcell.getCellType()!=CellType.STRING){
					continue;
				}
				String strcurcell = curcell.getStringCellValue();
//				String strlastcell = lastcell.getStringCellValue();
				if(nextcell == null){
					ismergebreak = true;
				}else{
					String strnextcell = nextcell.getStringCellValue();
					if(strcurcell != null && strnextcell !=null && strcurcell.equals(strnextcell)){
						if(isextendpercol){
							Cell percell = nextrow.getCell(icol-1);	//下一行前一列cell
							if(percellmergerange==null || !percellmergerange.equals(getMergedRegion(percell))){
								ismergebreak = true;
							}
						}
					}else{
						ismergebreak = true;
					}
				}
			}
			
			//处理合并行,并初始化相关参数
			if(ismergebreak){
				if(istartmergerow < irow){
					CellRangeAddress region = new CellRangeAddress(istartmergerow, irow, icol, icol);
					sheet.addMergedRegion(region);
				}
				
				istartmergerow = irow+1;
				if(isextendpercol){
					if(istartmergerow<=iendrow)
					percellmergerange = getMergedRegion(sheet.getRow(istartmergerow).getCell(icol-1));
				}
			}
		}
	}
	
	/**
	 * 根据给出的位置信息替换其中变量，将其中以String型显示的数字，改为数字类型
	 * @param sheet		sheet
	 * @param region	位置
	 */
	public static void parseStr2Db4Range(XSSFSheet sheet, CellRangeAddress region){
		for(int irow = region.getFirstRow(); irow <= region.getLastRow(); irow++){
			Row row = sheet.getRow(irow);
			for(int icol = region.getFirstColumn(); icol <= region.getLastColumn(); icol++){
				Cell cell = row.getCell(icol);
				if(cell.getCellType() == CellType.STRING){
					if(CustomMaths.isNumeric(cell.getStringCellValue())){
						Double cellvalue = Double.parseDouble(cell.getStringCellValue());
						cell.setCellValue(cellvalue);
					}
				}
			}
		}
	}
	
	/**
	 * 将sheet中所有以字符型显示的数字，调整为数字型
	 * @param sheet
	 */
	public static void parseStr2Db4Sheet(XSSFSheet sheet){
		Iterator itrow = sheet.rowIterator();
		while(itrow.hasNext()){
			Row row = (Row) itrow.next();
			Iterator itcol = row.cellIterator();
			while(itcol.hasNext()){
				Cell cell = (Cell) itcol.next();
				if(cell.getCellType() == CellType.STRING){
					if(CustomMaths.isNumeric(cell.getStringCellValue())){
						Double cellvalue = Double.parseDouble(cell.getStringCellValue());
						cell.setCellValue(cellvalue);
					}
				}
			}
		}
	}
	
	/**
	 * 删除两个行之内的全部的合并单元格的内容
	 * @param sheet
	 * @param istartrow
	 * @param iendrow
	 */
	public static void removeMergerRegion(XSSFSheet sheet, int istartrow, int iendrow){
		//删除合并单元格
        for(int irange=0; irange<sheet.getNumMergedRegions(); irange++){
        	CellRangeAddress range = sheet.getMergedRegion(irange);
        	if((range.getFirstRow()>=istartrow && range.getFirstRow() <= iendrow) ||
        			(range.getLastRow()>=istartrow && range.getLastRow() <= iendrow)){
        		sheet.removeMergedRegion(irange--);
        	}
        }
	}

    /**
     * 替换单元格
     * @param cell
     * @param map
     */
    public static void replaceCell(Cell cell, Map<String, Object> map){
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                //如果为时间格式的内容
//                if (HSSFDateUtil.isCellDateFormatted(cell)) {
//                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
//                    value=sdf.format(HSSFDateUtil.getJavaDate(cell.
//                            getNumericCellValue())).toString();
//                    break;
//                } else {
//                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
//                }
                break;
            case STRING: // 字符串Cell
                String cellValue = cell.getStringCellValue();
                String reValue = VarUtil.replaceText(cellValue, map);
                cell.setCellValue(new XSSFRichTextString(reValue));
                break;
            case BOOLEAN: // Boolean
                //value = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                //value = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                //value = "";
                break;
            case ERROR: // 故障
                //value = "非法字符";
                break;
            default:
                //value = "未知类型";
                break;
        }
    }
    
    /**
     * 替换图片
     * @param sheet
     * @param map
     * @throws IOException
     */
    public static void replacePictures4Sheet(XSSFSheet sheet, Map<String, Object> map) throws IOException{
    	if(sheet.getDrawingPatriarch() != null){
	    	for(Shape shape:sheet.getDrawingPatriarch().getShapes()){
	        	if(shape instanceof XSSFPicture){
	        		Object objpic = map.get(shape.getShapeName());
	        		if(objpic != null){
	    				if(objpic instanceof byte[]){
	    					OutputStream os = ((XSSFPicture) shape).getPictureData().getPackagePart().getOutputStream();
	    					os.write((byte[])objpic);
	    					((XSSFPicture) shape).getPictureData().getPackagePart().getPackage().save(os);
	    				}
					}
	        	}
	        }
    	}
    }

    /**
	 * 替换固定格式得excel模板
	 * @param is
	 * @param map
	 * @return
	 * @throws IOException
	 */
	public static XSSFWorkbook replaceFixedXlsx(InputStream is, Map<String, Object> map, boolean isautomergecol) throws IOException {
	    XSSFWorkbook workbook = new XSSFWorkbook(is);
	    //workbook.setForceFormulaRecalculation();
	    replaceWookbook(workbook, map, isautomergecol);
	    //合并单元格
	    
	    return workbook;
	}

	/**
     * 替换行
     * @param row
     * @param map
     */
    public static void replaceRow(Row row, Map<String, Object> map){
        Iterator<Cell> cellIterator = row.iterator();
        while(cellIterator.hasNext()){
            Cell cell = cellIterator.next();
            replaceCell(cell, map);
        }
    }

    /**
     * 替换sheet
     * @param sheet
     * @param map
     */
    public static void replaceSheet(XSSFSheet sheet, Map<String, Object> map){
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            replaceRow(row, map);
        }
    }

    /**
     * 替换workbook
     * @param workbook
     * @param map
     * @throws IOException 
     */
    public static void replaceWookbook(XSSFWorkbook workbook, Map<String, Object> map, boolean isautomergecol) throws IOException{
        Iterator<Sheet> sheetIterator = workbook.iterator();
        while(sheetIterator.hasNext()){
            XSSFSheet sheet = (XSSFSheet) sheetIterator.next();
            for(int i=0; i<sheet.getLastRowNum(); i++){
    			XSSFRow row = sheet.getRow(i);
    			if(row==null) sheet.createRow(i);
    		}
            sheet.setForceFormulaRecalculation(true);
            replaceSheet(sheet, map);
            replaceSheet4Loop(sheet, map, isautomergecol);
            replacePictures4Sheet(sheet, map);
            parseStr2Db4Sheet(sheet);
        }
    }

    /**
	 * @param in	输入文件流
	 * @param out	输出文件流
	 * @param varmap	变量库
	 * @throws IOException 
	 */
	public static void repalceXlsx2IO(InputStream in, OutputStream out, Map<String,Object> varmap, boolean isautomergecol) throws IOException{
		XSSFWorkbook xlsx = replaceFixedXlsx(in, varmap, isautomergecol);
		xlsx.write(out);
		xlsx.close();
	}

	/**
     * 替换需要循环取值的行
     * @param sheet
     * @param map
     */
    public static void replaceSheet4Loop(XSSFSheet sheet, Map<String, Object> map, boolean isautomergecol){
        List<Row> rowList = getLoopRows(sheet, map);
        for(int i=0; i<rowList.size(); i++){
        	Iterator<Row> rowIterator = sheet.iterator();
			while(rowIterator.hasNext()){
				Row row = rowIterator.next();
	            if(isLoopRow(row, map)){
		            Cell cell = row.getCell(0);
		            String strValue = cell.getStringCellValue();
		            String strDocKey = VarUtil.getVar(strValue, VarUtil.defaultRegex);    //文档中的变量key
		            //替换第一个单元格内的变量
		            if(strDocKey!=null){
		                cell.setCellValue(strValue.replace(strDocKey, ""));      //将第一个单元格的文档内容替换为不含key的值
		            }
		            //得到map的key
		            String strMapKey = strDocKey.substring(2,strDocKey.length()-1);
		            Object obj = map.get(strMapKey);
		            if(obj != null && obj instanceof List){
		            	List<Row> fromrowlist = new ArrayList<Row>();
		                fromrowlist.add(row);
		                List<Map<String,Object>> mapList = (List<Map<String,Object>>)obj;
		                int startRowNum = row.getRowNum();
		                int insertRowNum = mapList.size()>0?mapList.size()-1:0;
		                int endRowNum = sheet.getLastRowNum();
		                //将循环行到结束行后移
		                if(startRowNum<endRowNum && insertRowNum>0){
		                	sheet.copyRows(startRowNum+1, endRowNum, startRowNum+1+insertRowNum+endRowNum-startRowNum, new CellCopyPolicy());
		                	//删除合并单元格
		                	removeMergerRegion(sheet, startRowNum+1, endRowNum);
		                }
		                //移动图片
		                if(sheet.getDrawingPatriarch()!=null){
		                	for(Shape shape:sheet.getDrawingPatriarch().getShapes()){
		                    	if(shape instanceof XSSFPicture){
		                    		if(((XSSFPicture) shape).getClientAnchor().getRow1()>=startRowNum ){
		                    			((XSSFPicture) shape).getClientAnchor().setRow1(((XSSFPicture) shape).getClientAnchor().getRow1()+insertRowNum);
		                    			((XSSFPicture) shape).getClientAnchor().setRow2(((XSSFPicture) shape).getClientAnchor().getRow2()+insertRowNum);
		                    		}
		                    	}
		                    }
		                }
		                
		                //赋值循环行信息
		                for(int irow = startRowNum; irow < startRowNum+insertRowNum; irow++){
		                	sheet.copyRows(startRowNum, startRowNum, irow+1, new CellCopyPolicy());
		                }
		                if(startRowNum<endRowNum && insertRowNum>0){
		                	//将移动走的表格尾部移动回增长后的位置
		                    sheet.copyRows(endRowNum+1+insertRowNum, sheet.getLastRowNum(), startRowNum+insertRowNum+1,new CellCopyPolicy());
		                    //删除合并单元格
		                    removeMergerRegion(sheet, endRowNum+1+insertRowNum, sheet.getLastRowNum());
		                    //删除掉多余的行
		                    for(int irow = endRowNum+1+insertRowNum; irow<=sheet.getLastRowNum(); irow++){
		                    	sheet.removeRow(sheet.getRow(irow));
		                    }
		                }
		                //替换变量
		                for(Map<String, Object> valMap : mapList){
		                    replaceRow(sheet.getRow(startRowNum++), valMap);
		                }
		                CellRangeAddress region = new CellRangeAddress(row.getRowNum(), startRowNum>row.getRowNum()?startRowNum-1:row.getRowNum(), row.getFirstCellNum(), row.getLastCellNum());
		                if(isautomergecol)autoMergeCol4Range(sheet, region);
		                
		            }
		            break;		            
	            }
			}
        }
    }
    
    /**
     * @author Xuwd 2019/01/22
     * @param sheetName sheet名称
     * @param titleList	表头集合
     * @param dataList	数据集合
     * @param dataIndexMap	数据对应字段位置map
     * @param startNum	数据起始行数
     * @param wb		excel
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,List<HashMap<String,Object>> titleList,List<HashMap<String,Object>> dataList, HashMap<String,Integer> dataIndexMap, Integer startNum, HSSFWorkbook wb){
    	
    	// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
    	if(wb == null){
    		wb = new HSSFWorkbook();
    	}
    	// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
    	HSSFSheet sheet = wb.createSheet(sheetName);
    	// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    	HSSFRow row = sheet.createRow(0);
    	// 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
    	//声明列对象
    	HSSFCell cell = null;
    	for (int i = 0; i < startNum; i++) {
    		row = sheet.createRow(i);
    		for (int j = 0; j < titleList.size(); j++) {
    			HashMap<String, Object> titleMap = titleList.get(j);
    			String headerName = titleMap.get("headerName")!=null?titleMap.get("headerName").toString():"";
    			String level = titleMap.get("level").toString();
    			String x_s_str = titleMap.get("x_s").toString();
    			String x_e_str = titleMap.get("x_e").toString();
    			Boolean have_child = (Boolean) titleMap.get("child");
    			int y_s = 0;
    			int y_e = 0;
    			int x_s = 0;
    			int x_e = 0;
    			
    			y_s = Integer.valueOf(level);
    			x_s = Integer.valueOf(x_s_str);
    			x_e = Integer.valueOf(x_e_str);
    			if (have_child) {
        			y_e = y_s;
				} else {
					y_e = startNum - 1;
				}
    			CellRangeAddress region = new CellRangeAddress(y_s, y_e, x_s, x_e);// 下标从0开始 起始行号，终止行号， 起始列号，终止列号
    			if (i==y_s&&(y_s!=y_e||x_s!=x_e)) {
    				sheet.addMergedRegion(region);
    			}
    			
    			if (i==y_s) {
    				cell = row.createCell(x_s);
                    cell.setCellValue(headerName);
                    cell.setCellStyle(style);
				}
			}
		}
    	
    	//创建内容
    	for(int i=0;i<dataList.size();i++){
    		row = sheet.createRow(i + startNum);
    		HashMap<String, Object> dataMap = dataList.get(i);
    		if(dataMap!=null) {
    			for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
    				//Map.entry<Integer,String> 映射项（键-值对）  有几个方法：用上面的名字entry
    				//entry.getKey() ;entry.getValue(); entry.setValue();
    				//map.entrySet()  返回此映射中包含的映射关系的 Set视图。
    				//将内容按顺序赋给对应的列对象
    				String key = entry.getKey();
    				String value = entry.getValue()!=null?entry.getValue().toString():"";
    				if (dataIndexMap.containsKey(key)) {
    					int index = dataIndexMap.get(key);
    					row.createCell(index).setCellValue(value);
    				}
    			}
    		}
    	}
    	return wb;
    }
    
    /**
     * 合并workbook
     * @param destWb	目标workbook
     * @param srcWb		源workbook
     * @throws IOException
     */
    public static void concatWorkbook(XSSFWorkbook destWb, XSSFWorkbook srcWb) throws IOException{
    	int numberOfSheets = destWb.getNumberOfSheets();
    	List<String> sheetNames = Lists.newArrayList();
		for (int i = 0; i < numberOfSheets; i++) {
			//初始化被合并文件sheet names
			sheetNames.add(destWb.getSheetName(i));
		}
		int numberOfToBeMergeSheets = srcWb.getNumberOfSheets();
		for (int i = 0; i < numberOfToBeMergeSheets; i++) {
			XSSFSheet sheet = srcWb.getSheetAt(i);
			String sheetName = sheet.getSheetName();
			sheetName = sheetNameCanUse(sheetName, sheetNames);
			XSSFSheet newSheet = destWb.createSheet(sheetName);
			copySheet(newSheet, sheet);
			if (SheetVisibility.HIDDEN == srcWb.getSheetVisibility(i)) {
				//sheet 隐藏
				destWb.setSheetHidden(destWb.getSheetIndex(sheetName), true);
			}
		}
    }
	
    /**
     * 复制sheet的所有内容
     * @param destSheet
     * @param srcsheet
     */
	public static void copySheet(XSSFSheet destSheet, XSSFSheet srcsheet) {
		copySheetsStyle(destSheet, srcsheet);
		copySheetsData(destSheet, srcsheet, true);
		copySheetsDrawing(destSheet, srcsheet);
		copySheetsMergedRegions(destSheet, srcsheet);
	}
	
	/**
	 * 复制sheet中的picture和chart
	 * @param destSheet	目标sheet
	 * @param srcsheet	源sheet
	 */
	private static void copySheetsDrawing(XSSFSheet destSheet, XSSFSheet srcsheet){
		if(srcsheet.getDrawingPatriarch() != null){
			XSSFDrawing destdraw = destSheet.createDrawingPatriarch();
	    	for(Shape shape:srcsheet.getDrawingPatriarch().getShapes()){
	        	if(shape instanceof XSSFPicture){
	        		XSSFPicture picture = (XSSFPicture) shape;
	        		XSSFClientAnchor anchor = picture.getPreferredSize();
	        		int index = destSheet.getWorkbook().addPicture(picture.getPictureData().getData(), XSSFWorkbook.PICTURE_TYPE_PNG);
	        		destdraw.createPicture(anchor, index);
	        	}else if(shape instanceof XSSFGraphicFrame){
	        		for(XSSFChart srcChart : ((XSSFGraphicFrame) shape).getDrawing().getCharts()){
	        			ClientAnchor anchor =  srcChart.getGraphicFrame().getAnchor();
	        			XSSFChart destChart = destdraw.createChart(anchor);
//	        			destChart.importContent(srcChart);这个方法也可以实现，可替换destChart.getCTChart().set(srcChart.getCTChart().copy())
	        			destChart.getCTChart().set(srcChart.getCTChart().copy());
	        			destChart.getGraphicFrame().setName(srcChart.getGraphicFrame().getName());
	        			destChart.getCTChartSpace().set(srcChart.getCTChartSpace().copy());
	        		}
	        		
	        	}
	        }
    	}
	}
	
	/**
	 * 复制sheet合并区域
	 * @param destSheet	目标sheet
	 * @param srcsheet	源sheet
	 */
	private static void copySheetsMergedRegions(XSSFSheet destSheet, XSSFSheet srcsheet){
		List<CellRangeAddress> mergers = srcsheet.getMergedRegions();
		for(int i = 0; i <mergers.size() ; i++){
			CellRangeAddress mergedRegion = mergers.get(i);
			if (mergedRegion != null) {
				CellRangeAddress newMergedRegion = new CellRangeAddress(
						mergedRegion.getFirstRow(),
						mergedRegion.getLastRow(), mergedRegion
						.getFirstColumn(), mergedRegion
						.getLastColumn());
				destSheet.addMergedRegion(newMergedRegion);
			}
		}
	}

	
	/**
	 * 复制sheet的样式
	 * @param destSheet	目标sheet
	 * @param srcsheet	源sheet
	 */
	private static void copySheetsStyle(XSSFSheet destSheet, XSSFSheet srcsheet) {
		//公式计算
		destSheet.setForceFormulaRecalculation(true);
		//sheet tab颜色
		XSSFColor tabColor = srcsheet.getTabColor();
		if (Objects.nonNull(tabColor)) {
			destSheet.setTabColor(srcsheet.getTabColor());
		}
		//条件表达式
		XSSFSheetConditionalFormatting scf = srcsheet.getSheetConditionalFormatting();
		SheetConditionalFormatting toSheetConditionFormat = destSheet.getSheetConditionalFormatting();
		for (int i = 0; i < scf.getNumConditionalFormattings(); i++) {
			toSheetConditionFormat.addConditionalFormatting(scf.getConditionalFormattingAt(i));
		}
		//冻结行列信息
		PaneInformation paneInformation = srcsheet.getPaneInformation();
		if (Objects.nonNull(paneInformation)) {
			destSheet.createFreezePane(paneInformation.getHorizontalSplitPosition(), paneInformation.getVerticalSplitPosition()
					, paneInformation.getHorizontalSplitTopRow(), paneInformation.getVerticalSplitLeftColumn());
		}
	}

	/**
	 * 复制sheet数据
	 * @param destSheet	目标sheet
	 * @param srcsheet	源sheet
	 * @param copyStyle	是否复制单元格样式
	 */
	private static void copySheetsData(XSSFSheet destSheet, XSSFSheet srcsheet,
			boolean copyStyle) {
		int maxColumnNum = 0;
		destSheet.setDefaultColumnWidth(srcsheet.getDefaultColumnWidth());
		Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>()
				: null;
		for (int i = srcsheet.getFirstRowNum(); i <= srcsheet.getLastRowNum(); i++) {
			XSSFRow srcRow = srcsheet.getRow(i);
			XSSFRow destRow = destSheet.createRow(i);

			if (srcRow != null) {
				//行隐藏
				destRow.setZeroHeight(srcRow.getZeroHeight());
				copyRow(srcsheet, destSheet, srcRow, destRow,
						styleMap);
				if (srcRow.getLastCellNum() > maxColumnNum) {
					maxColumnNum = srcRow.getLastCellNum();
				}
			}
		}
		for (int i = 0; i <= maxColumnNum; i++) {    //设置列宽
			destSheet.setColumnWidth(i, srcsheet.getColumnWidth(i));
			//列隐藏
			destSheet.setColumnHidden(i, srcsheet.isColumnHidden(i));
		}
	}

	/**
	 * 复制行
	 * @param srcSheet	源sheet
	 * @param destSheet	目标sheet
	 * @param srcRow	源行
	 * @param destRow	目标行
	 * @param styleMap	样式map
	 */
	private static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet,
			XSSFRow srcRow, XSSFRow destRow,
			Map<Integer, CellStyle> styleMap) {
		
		destRow.setHeight(srcRow.getHeight());
		//如果copy到另一个sheet的起始行数不同
//		int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
		int jn = srcRow.getFirstCellNum() < 0 ? 0 : srcRow.getFirstCellNum();
		for (int j = jn; j <= srcRow.getLastCellNum(); j++) {
			XSSFCell oldCell = srcRow.getCell(j);
			XSSFCell newCell = destRow.getCell(j);
			if (oldCell != null) {
				if (newCell == null) {
					newCell = destRow.createCell(j);
				}
				copyCell(oldCell, newCell, styleMap);
			}
		}
	}

		
	/**
	 * 把原来的Sheet中cell（列）的样式和数据类型复制到新的sheet的cell（列）中
	 *
	 * @param srcCell 源cell
	 * @param destCell 目标cell
	 * @param styleMap 样式map
	 */
	private static void copyCell(XSSFCell srcCell, XSSFCell destCell,
			Map<Integer, CellStyle> styleMap) {
		if (styleMap != null) {
			int stHashCode = srcCell.getCellStyle().hashCode();
			CellStyle newCellStyle = styleMap.get(stHashCode);
			if (newCellStyle == null) {
				newCellStyle = destCell.getSheet().getWorkbook()
						.createCellStyle();
				newCellStyle.cloneStyleFrom(srcCell.getCellStyle());
				styleMap.put(stHashCode, newCellStyle);
			}
			destCell.setCellStyle(newCellStyle);
		}
		switch (srcCell.getCellType()) {
			case STRING:
				destCell.setCellValue(srcCell.getStringCellValue());
				break;
			case NUMERIC:
				destCell.setCellValue(srcCell.getNumericCellValue());
				break;
			case BLANK:
				destCell.setCellType(CellType.BLANK);
				break;
			case BOOLEAN:
				destCell.setCellValue(srcCell.getBooleanCellValue());
				break;
			case ERROR:
				destCell.setCellErrorValue(srcCell.getErrorCellValue());
				break;
			case FORMULA:
				destCell.setCellFormula(srcCell.getCellFormula());
				break;
			default:
				break;
		}
	}

	/**
	 * 判定sheet名称是否存在
	 * @param sheetName	新的sheet名
	 * @param sheetNames 原workbook中的sheet名
	 * @return 可用的sheet名
	 */
	private static String sheetNameCanUse(String sheetName, List<String> sheetNames) {
		int suffix = 0;
		String nameCanUse = sheetName;
		while (sheetNames.contains(nameCanUse)) {
			nameCanUse = sheetName + "(" + suffix + ")";
			suffix += 1;
		}
		sheetNames.add(nameCanUse);
		return nameCanUse;
	}
	
	/**
	 * 设置默认表格的表头样式
	 * @param cs
	 */
	private static void setDefHeadCellStyle(XSSFCellStyle cs){
		cs.setAlignment(HorizontalAlignment.CENTER);
//		font.setBold(true);
		cs.getFont().setBold(true);
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
	}
	
	/**
	 * 设置默认表格的标题样式
	 * @param cs
	 */
	private static void setDefBodyCellStyle(XSSFCellStyle cs){
		cs.setBorderBottom(BorderStyle.THIN);
		cs.setBorderTop(BorderStyle.THIN);
		cs.setBorderLeft(BorderStyle.THIN);
		cs.setBorderRight(BorderStyle.THIN);
	}
	
	/**
	 * 将数据集写入指定sheet
	 * @param sheet 指定sheet
	 * @param datas 数据集
	 */
	public static void writeSheet(XSSFSheet sheet, List<Map<String,Object>> datas){
		XSSFCellStyle hcs = sheet.getWorkbook().createCellStyle();
		XSSFFont hfont = sheet.getWorkbook().createFont();
		hcs.setFont(hfont);
		setDefHeadCellStyle(hcs);
		XSSFCellStyle bcs = sheet.getWorkbook().createCellStyle();
		XSSFFont bfont = sheet.getWorkbook().createFont();
		bcs.setFont(bfont);
		setDefBodyCellStyle(bcs);
		
		XSSFCellStyle bcsdate = sheet.getWorkbook().createCellStyle();
		XSSFFont bfontdate = sheet.getWorkbook().createFont();
		bcs.setFont(bfontdate);
		setDefBodyCellStyle(bcsdate);
		bcsdate.setDataFormat(sheet.getWorkbook().createDataFormat().getFormat("yyyy-MM-dd"));
		
		if(sheet == null || datas.size()==0){
			return;
		}
		XSSFRow lastrow = sheet.getRow(sheet.getLastRowNum());
		int currownum = 0;
		if(lastrow != null){
			currownum = sheet.getLastRowNum()+ 1;
		}
		int columnnum = 0;
		Set<String> keyset = null;
		//初始化表格头，判定表格共有几列
		for(Map<String,Object> data: datas){
			if(columnnum < data.size()){
				columnnum = data.size();
				keyset = data.keySet();
			}
		}
		if(keyset == null){
			return;
		}
		int columnIndex = 0;
		XSSFRow row = sheet.createRow(currownum++);
		//用表头信息在sheet中新建一行
		for(String strKey : keyset){
			XSSFCell cell = row.createCell(columnIndex++);
			cell.setCellStyle(hcs);
			cell.setCellValue(strKey);
		}
		//将map中数据写入sheet下面的行中
		for(Map<String,Object> data: datas){
			row = sheet.createRow(currownum++);
			columnIndex = 0;
			for(String strKey : keyset){
				Object obvalue = data.get(strKey);
				XSSFCell cell = row.createCell(columnIndex++);
				cell.setCellStyle(bcs);
//				System.out.println(cell.getCellStyle());
//				cell.setCellStyle(arg0);
				if(obvalue==null){
					cell.setCellType(CellType.BLANK);
				}else{
					if(obvalue instanceof Integer){
						cell.setCellValue(((Integer)obvalue).intValue());
					}else if(obvalue instanceof Double){
						cell.setCellValue(((Double)obvalue).doubleValue());
					}else if(obvalue instanceof Long){
						cell.setCellValue(((Long)obvalue).longValue());
					}else if(obvalue instanceof Date){
						cell.setCellValue(((Date)obvalue));
						cell.setCellStyle(bcsdate);
					}else {
						cell.setCellValue(String.valueOf(obvalue));
					}
				}
			}
		}
	}
	
	/**
	 * 在workbook里面新增一个sheet并将数据写入
	 * @param wb			workbook
	 * @param datas			数据
	 * @param sheetName		sheet名
	 */
	public static void addSheet4Datas(XSSFWorkbook wb, List<Map<String,Object>> datas, String sheetName){
		XSSFSheet sheet = wb.createSheet(sheetName);
		writeSheet(sheet, datas);
	}
    
}
